﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace PookSistener.Customer
{
    public partial class frmCustomerProduct : Form
    {
        private OleDbConnection conn = null;
        private DataSet dataSet = new DataSet();

        private string MODE = "";

        public frmCustomerProduct()
        {
            InitializeComponent();
        }

        private void frmCustomerProduct_Load(object sender, EventArgs e)
        {
            conn = new OleDbConnection(PookSistener.Properties.Settings.Default.DatabaseConnectionString);
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }

            conn.Open();

            OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM Customer", conn);
            adapter.Fill(dataSet, "Customer");

            dataGridView1.AutoGenerateColumns = false;
            dataGridView1.DataSource = dataSet.Tables["Customer"];

            adapter = new OleDbDataAdapter("SELECT * FROM Product", conn);
            adapter.Fill(dataSet, "Product");


            comboBox1.DataSource = dataSet.Tables["Product"];
            comboBox1.DisplayMember = "Name";
            comboBox1.ValueMember = "id";

            // Resize Column
            int scrollBarWidth = SystemInformation.VerticalScrollBarWidth + 5;
            int rowHeaderWidth = dataGridView1.RowHeadersWidth + 3;

            int width = dataGridView1.Width - scrollBarWidth;

            int remainingWidth = 0;

            dataGridView1.Columns["tName"].Width = (width - remainingWidth);


            // Get Customer Product
            adapter = new OleDbDataAdapter("SELECT CustomerProduct.*, Product.Name AS ProductName FROM CustomerProduct LEFT JOIN Product ON CustomerProduct.ProductId = Product.ID WHERE CustomerId=" + txtCustomerName.Tag, conn);


            adapter.Fill(dataSet, "CustomerProduct");

            dataGridView2.AutoGenerateColumns = false;
            dataGridView2.DataSource = dataSet.Tables["CustomerProduct"];

            dataGridView2_Resize(null, null);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            dataGridView1.Top = txtCustomerName.Top + txtCustomerName.Height + 10;
            dataGridView1.Left = txtCustomerName.Left;
            dataGridView1.Width = dataGridView2.Width;
            dataGridView1.Visible = !dataGridView1.Visible;
        }

        private void dataGridView1_Resize(object sender, EventArgs e)
        {
            int scrollBarWidth = SystemInformation.VerticalScrollBarWidth + 5;
            int rowHeaderWidth = dataGridView1.RowHeadersWidth + 3;

            int width = dataGridView1.Width - scrollBarWidth;

            int remainingWidth = 0;

            dataGridView1.Columns["tName"].Width = (width - remainingWidth);
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (dataGridView1.Rows.Count > 0)
            {
                if (dataGridView1.SelectedRows.Count > 0)
                {
                    DataGridViewRow row = dataGridView1.SelectedRows[0];

                    string id = row.Cells["ID"].Value.ToString();
                    string name = row.Cells["tName"].Value.ToString();


                    txtCustomerName.Text = name;
                    txtCustomerName.Tag = id;

                }
            }

            dataGridView1.Visible = false;
        }

        private void frmCustomerProduct_MouseClick(object sender, MouseEventArgs e)
        {
            dataGridView1.Visible = false;
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            MODE = "ADD";
            panel1.Left = (int)((this.Width - panel1.Width) / 2);
            panel1.Top = (int)((this.Height - panel1.Height) / 2);

            EorDButton(false);

            txtPrice.Text = "0.00";

            comboBox1.Enabled = true;

            panel1.Visible = true;

            txtPrice.Focus();
            txtPrice.SelectAll();
        }

        private void EorDButton(bool isEnable)
        {
            btnAdd.Enabled = isEnable;
            btnEdit.Enabled = isEnable;
            btnDelete.Enabled = isEnable;
            dataGridView2.Enabled = isEnable;
            dataGridView1.Visible = false;
            button4.Enabled = isEnable;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            EorDButton(true);
            panel1.Visible = false;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            double price = 0;
            try
            {
                price = Convert.ToDouble(txtPrice.Text);
            }
            catch (Exception ex)
            {
                MessageBox.Show("กรุณาใส่ราคาเป็นตัวเลข");
                return;
            }

            if (price <= 0)
            {
                MessageBox.Show("กรุณาใส่ราคาให้เป็นค่าที่มากกว่า 0 เท่านั้น");
                return;
            }

            if ("ADD".Equals(MODE))
            {
                string sql = @"INSERT INTO CustomerProduct(CustomerId, ProductId, Price, PriceDate) VALUES(@CustomerId, @ProductId, @Price, @PriceDate)";
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                cmd.Parameters.Clear();

                cmd.Parameters.Add("@CustomerId", OleDbType.VarChar).Value = txtCustomerName.Tag;
                cmd.Parameters.Add("@ProductId", OleDbType.VarChar).Value = comboBox1.SelectedValue;
                cmd.Parameters.Add("@Price", OleDbType.Double).Value = price;
                cmd.Parameters.Add("@PriceDate", OleDbType.Date).Value = System.DateTime.Now.ToShortDateString();

                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (OleDbException ex)
                {
                    MessageBox.Show("ใส่ข้อมูลสินค้าซ้ำ กรุณาลองให่อีกครั้ง", "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                MessageBox.Show("ทำการเพิ่มเรียบร้อยแล้ว");
                txtPrice.Text = "0.00";
            }
            else if ("EDIT".Equals(MODE))
            {
                string sql = @"UPDATE CustomerProduct SET Price = @MyPrice , PriceDate = @PriceDate WHERE CustomerId=@CustomerId AND ProductId=@ProductId";
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                cmd.Parameters.Clear();

                cmd.Parameters.Add("@MyPrice", OleDbType.Double).Value = price;
                cmd.Parameters.Add("@PriceDate", OleDbType.Date).Value = System.DateTime.Now.ToShortDateString();
                cmd.Parameters.Add("@CustomerId", OleDbType.VarChar).Value = txtCustomerName.Tag;
                cmd.Parameters.Add("@ProductId", OleDbType.VarChar).Value = comboBox1.SelectedValue;
                

                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (OleDbException ex)
                {
                    MessageBox.Show("ไม่สามารถแก้ไขข้อมูลได้\n" + ex.Message, "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                MessageBox.Show("ทำการแก้ไขเรียบร้อยแล้ว");
            }

            dataSet.Tables["CustomerProduct"].Clear();
            dataGridView2.DataSource = null;

            OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT CustomerProduct.*, Product.Name AS ProductName FROM CustomerProduct LEFT JOIN Product ON CustomerProduct.ProductId = Product.ID WHERE CustomerId=" + txtCustomerName.Tag, conn);
            adapter.Fill(dataSet, "CustomerProduct");

            dataGridView2.AutoGenerateColumns = false;
            dataGridView2.DataSource = dataSet.Tables["CustomerProduct"];
        }

        private void dataGridView2_Resize(object sender, EventArgs e)
        {
            int scrollBarWidth = SystemInformation.VerticalScrollBarWidth + 5;
            int rowHeaderWidth = dataGridView2.RowHeadersWidth + 3;

            int width = dataGridView2.Width - scrollBarWidth;

            int remainingWidth = 150;

            dataGridView2.Columns["colProductName"].Width = (width - remainingWidth);
            dataGridView2.Columns["colPrice"].Width = (remainingWidth);
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            MODE = "EDIT";
            panel1.Left = (int)((this.Width - panel1.Width) / 2);
            panel1.Top = (int)((this.Height - panel1.Height) / 2);

            EorDButton(false);

            txtPrice.Text = "0.00";

            DataGridViewSelectedRowCollection rows = dataGridView2.SelectedRows;
            if (rows.Count > 0)
            {
                DataGridViewRow row = rows[0];

                object productId = row.Cells["colProductId"].Value;
                string price = row.Cells["colPrice"].Value.ToString();

                comboBox1.SelectedValue = productId;
                comboBox1.Enabled = false;
                txtPrice.Text = price;

                panel1.Visible = true;

                txtPrice.Focus();
                txtPrice.SelectAll();
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            MODE = "DELETE";
            DataGridViewSelectedRowCollection rows = dataGridView2.SelectedRows;
            if (rows.Count > 0)
            {
                if (DialogResult.Yes == MessageBox.Show("คุณต้องการลบข้อมูลนี้ใช่หรือไม่", "คำถาม", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
                {

                    object productId = rows[0].Cells["colProductId"].Value;

                    string sql = @"DELETE FROM CustomerProduct WHERE CustomerId=@CustomerId AND ProductId=@ProductId";
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.Parameters.Clear();

                    cmd.Parameters.Add("@CustomerId", OleDbType.VarChar).Value = txtCustomerName.Tag;
                    cmd.Parameters.Add("@ProductId", OleDbType.VarChar).Value = productId;

                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException ex)
                    {
                        MessageBox.Show("ไม่สามารถลบได้ กรุณาลองให่อีกครั้ง\n" + ex.Message, "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    MessageBox.Show("ทำการลบข้อมูลเรียบร้อยแล้ว");
                    dataSet.Tables["CustomerProduct"].Clear();
                    dataGridView2.DataSource = null;

                    OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT CustomerProduct.*, Product.Name AS ProductName FROM CustomerProduct LEFT JOIN Product ON CustomerProduct.ProductId = Product.ID WHERE CustomerId=" + txtCustomerName.Tag, conn);
                    adapter.Fill(dataSet, "CustomerProduct");

                    dataGridView2.AutoGenerateColumns = false;
                    dataGridView2.DataSource = dataSet.Tables["CustomerProduct"];
                }
            }
        }
    }
}
